package com.ruoyi.suggestion.mapper;

import java.util.List;
import java.util.Map;

import com.ruoyi.common.core.page.TableDataInfo;
import com.ruoyi.suggestion.domain.CommunitySuggestion;
import org.apache.ibatis.annotations.Select;

/**
 * 社区建议Mapper接口
 * 
 * @author xianyu
 * @date 2025-04-02
 */
public interface CommunitySuggestionMapper 
{
    /**
     * 查询社区建议
     * 
     * @param id 社区建议主键
     * @return 社区建议
     */
    public CommunitySuggestion selectCommunitySuggestionById(Long id);

    /**
     * 查询社区建议列表
     * 
     * @param communitySuggestion 社区建议
     * @return 社区建议集合
     */
    public List<CommunitySuggestion> selectCommunitySuggestionList(CommunitySuggestion communitySuggestion);

    /**
     * 新增社区建议
     * 
     * @param communitySuggestion 社区建议
     * @return 结果
     */
    public int insertCommunitySuggestion(CommunitySuggestion communitySuggestion);

    /**
     * 修改社区建议
     * 
     * @param communitySuggestion 社区建议
     * @return 结果
     */
    public int updateCommunitySuggestion(CommunitySuggestion communitySuggestion);

    /**
     * 删除社区建议
     * 
     * @param id 社区建议主键
     * @return 结果
     */
    public int deleteCommunitySuggestionById(Long id);

    /**
     * 批量删除社区建议
     * 
     * @param ids 需要删除的数据主键集合
     * @return 结果
     */
    public int deleteCommunitySuggestionByIds(Long[] ids);

    @Select({
            "SELECT",
            "(SELECT COUNT(*) FROM community_suggestion WHERE YEAR(create_time) = YEAR(NOW())) AS annualSuggestions,",
            "(SELECT COUNT(*) FROM community_suggestion WHERE MONTH(create_time) = MONTH(NOW()) AND YEAR(create_time) = YEAR(NOW())) AS monthlySuggestions,",
            "(SELECT COUNT(*) FROM community_suggestion WHERE DATE(create_time) = CURDATE()) AS dailySuggestions,",
            "(SELECT IFNULL((SUM(CASE WHEN status = 'implemented' THEN 1 ELSE 0 END) / NULLIF(COUNT(*), 0)) * 100, 0) FROM community_suggestion) AS successRate,",
            "(SELECT COUNT(DISTINCT user_id) FROM community_suggestion WHERE create_time >= DATE_SUB(NOW(), INTERVAL 7 DAY)) AS activeUsers,",
            "(SELECT COUNT(*) FROM community_suggestion WHERE YEAR(create_time) = YEAR(NOW())) AS currentYear,",
            "(SELECT COUNT(*) FROM community_suggestion WHERE YEAR(create_time) = YEAR(NOW()) - 1) AS lastYear,",
            "(SELECT COUNT(*) FROM community_suggestion WHERE MONTH(create_time) = MONTH(NOW()) AND YEAR(create_time) = YEAR(NOW())) AS currentMonth,",
            "(SELECT COUNT(*) FROM community_suggestion WHERE MONTH(create_time) = MONTH(NOW()) - 1 AND YEAR(create_time) = YEAR(NOW())) AS lastMonth"
    })
    Map<String, Object> getDashboardStats();

    List<TableDataInfo> suggestVote();
}
